import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Random;


public class Scriptor {

	static final int numDoctors = 50;
	static final int numPatients = 200;
	static String allergies[] = {"Shellfish", "Peanuts", "Lortab", "Chocolate", "Homework", "Labor", "BS"};
	static String locations[] = {"McKay Dee", "Davis Medical Center", "Ogden Regional", "Lakeview", "Instacare"};
	static String firstNames[] = {"Cindy Lou", "Jean", "Chez", "Sergio", "Quayle", "Shaniqua", "Peter", "Marilyse", 
		"Ian", "Eric", "Christopher", "Justin", "Ellen", "Aubrie", "Alvin", "Simon", "Theodore",
		"Devon", "Kael", "Bob", "Adam", "Aiden", "Marnie", "Alaina", "Kathy", "Michael", "Elizabeth", "Brian"
	};
	static String lastNames[] = {"Who", "Luc Picard", "ValJean", "Magnifique", "Rodriguez", "Smith", "Jones", "Litster", "Gast", "Bell", "Harding", "Speirs", "Bair", "Wood",
		"Johnson", "Allen", "Thompson", "Brown", "Blosser", "McCuistion", "McDonald", "McMaster", "McHammer"
	};
	static String drSpecializations[] = {"Proctologist", "Dermatologist", "General Practitioner", "OB/Gyn", 
		"Carcinologist", "Optometrist", "Dentist", "Psychologist", "Psychiatrist", "Theoretical Neoneuralphysicist", "Timelord"
	};
	static String medicationNames[] = {"Lortab", "Ibuprofen", "Whiskey", "Chocolate", "Benadryl", "Morphine",
		"Tylenol", "Percoset", "Hyadrocortisone", "Epinephrine", "Penicillin", "Amoxycillin", "Sleep"
	};
	static String races[] = {
		"White", "Black", "Native American/Inuit", "Oriental", "Hispanic", "Triathlon", "Indy 500", "Marathon",
		"Hawaiian", "Other"
	};
	static String insurers[] = {
		"IHC", "Aetna", "Cigna", "Teamsters", "self"
	};
	static String bloodTypes[] = {
		"O+", "O-", "A+", "A-", "B+", "B-", "AB+", "AB-"
	};
	static String religions[] = {
		"LDS", "Catholic", "Hindu", "Buddhist", "Atheist", "Muslim", "Other", "Pastafarian"
	};
	static String countries[] = {
		"United States", "Mexico", "Canada", "Australia", "England", "Wakanda", "France"
	};
	static String states[] = {
		"Utah", "Colorado", "California", "Arkansas", "Nevada", "New York", "Florida"
	};
	static String cities[] = {
		"Ogden", "Layton", "Clearfield", "Springfield", "Rockville", "Jersey City", "Sim City", "Worchestershire"
	};
	static String visitReasons[] = {
		"Persistent cough", "Blood in urine", "Knee pain", "Herpes", "Pregnancy", "Skin problem",
		"Broken bone", "Car crash", "Paranoia", "Depression", "Plastic Surgery Consult", "Two Hearts", "Homework Overload", "Arrow in the Knee"
	};
	
	
	static Random randy = new Random(System.currentTimeMillis());
	static String doctors[][] = new String[numDoctors][4];
	static String patients[][] = new String[numPatients][14];
	
	//empty the tables first
	public static void ScriptDeletes(OutputStream os){
		StringBuffer sb = new StringBuffer();
		
		
		//write stuff here
		sb.append("DELETE FROM PatientAllergies\n");
		sb.append("DELETE FROM Visits\n");
		sb.append("DELETE FROM PatientMeds\n");
		sb.append("DELETE FROM Allergies\n");
		sb.append("DELETE FROM Locations\n");
		sb.append("DELETE FROM Doctors\n");
		sb.append("DELETE FROM Medications\n");
		sb.append("DELETE FROM Patients\n");
		
		sb.append("\n");
		
		byte[] sBytes = sb.toString().getBytes();
		try{
			os.write(sBytes);
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	//print the SQL for the allergies table to the output stream
	public static void ScriptAllergies(OutputStream os){
		
		StringBuffer sb = new StringBuffer();
		
		
		//write stuff here
		sb.append("INSERT INTO Allergies (AllergyName)\n" +
				"VALUES ('" + allergies[0] + "')");
		for(int i=1; i<allergies.length; i++){
			sb.append(", \n('" + allergies[i] + "')");
		}
		
		sb.append(";\n");
		sb.append("\n");
		
		byte[] sBytes = sb.toString().getBytes();
		try{
			os.write(sBytes);
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	//print the SQL for the locations table to the output stream
	public static void ScriptLocations(OutputStream os){
		StringBuffer sb = new StringBuffer();

		//write stuff here
		sb.append("INSERT INTO Locations (Name)\n" +
			"VALUES ('" + locations[0] + "')");
		for(int i=1; i<locations.length; i++){
			sb.append(", \n('" + locations[i] + "')");
		}
		
		sb.append(";\n");
		sb.append("\n");

		byte[] sBytes = sb.toString().getBytes();
		try{
			os.write(sBytes);
		}catch(IOException e){
			e.printStackTrace();
		}
		
	}
	
	//print the SQL for the doctors table to the output stream
	public static void ScriptDoctors(OutputStream os){
		StringBuffer sb = new StringBuffer();

		//write stuff here
		for(int i=0; i < numDoctors; i++){
			doctors[i][0] = firstNames[randy.nextInt(firstNames.length)];
			doctors[i][1] = lastNames[randy.nextInt(lastNames.length)];
			doctors[i][2] = "" + ((char)((char)(randy.nextInt(26)) + 'A'));
			doctors[i][3] = drSpecializations[randy.nextInt(drSpecializations.length)];
		}
		sb.append("INSERT INTO Doctors (FirstName, LastName, MiddleInitial, Specialization)\n" +
				"VALUES ('" + doctors[0][0] + 
				"', '" + doctors[0][1] + 
				"', '" + doctors[0][2] + 
				"', '" + doctors[0][3] + "')");
		
		for(int i=1; i < numDoctors; i++){
			sb.append(", \n('" + doctors[i][0] + 
					"', '" + doctors[i][1] + 
					"', '" + doctors[i][2] + 
					"', '" + doctors[i][3] + "')");
		}
		
		sb.append(";\n\n");

		byte[] sBytes = sb.toString().getBytes();
		try{
			os.write(sBytes);
		}catch(IOException e){
			e.printStackTrace();
		}
		
	}
	
	//print the SQL for the medications table to the output stream
	public static void ScriptMedications(OutputStream os){
		
		StringBuffer sb = new StringBuffer();

		//write stuff here
		sb.append("INSERT INTO Medications (Name)\n" +
				"VALUES ('" + medicationNames[0] + "')");
		for(int i=1; i<medicationNames.length; i++){
			sb.append(", \n('" + medicationNames[i] + "')");

		}
		
		sb.append(";\n\n");

		byte[] sBytes = sb.toString().getBytes();
		try{
			os.write(sBytes);
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	//print the SQL for the patients table to the output stream
	public static void ScriptPatients(OutputStream os){
		
		StringBuffer sb = new StringBuffer();

		//write stuff here
		for(int i=0; i < numPatients; i++){
			patients[i][0] = firstNames[randy.nextInt(firstNames.length)];
			patients[i][1] = lastNames[randy.nextInt(lastNames.length)];
			patients[i][2] = "" + ((char)((char)(randy.nextInt(26)) + 'A'));
			patients[i][3] = races[randy.nextInt(races.length)];
			patients[i][4] = randy.nextBoolean() == true ? "M" : "F";
			patients[i][5] = insurers[randy.nextInt(insurers.length)];
			patients[i][6] = "" + (randy.nextInt(12)+1) + "/" + (randy.nextInt(28)+1) + "/" + (randy.nextInt(100)+1912);
			patients[i][7] = bloodTypes[randy.nextInt(bloodTypes.length)];
			patients[i][8] = religions[randy.nextInt(religions.length)];
			patients[i][9] = randy.nextInt(5) < 4 ? countries[0] : countries[randy.nextInt(countries.length)];
			if(patients[i][9].equals(countries[0])){
				patients[i][10] = states[randy.nextInt(states.length)];
				patients[i][11] = cities[randy.nextInt(cities.length)];
				patients[i][12] = "" + (randy.nextInt(90000) + 10000);
				patients[i][13] = "" + (randy.nextInt(10000)+1) +
						(randy.nextInt(2) == 0 ?
								(randy.nextInt(2) == 0 ? " South " : " North ") + 
								randy.nextInt(10000) +
								(randy.nextInt(2) == 0 ? " East" : " West") :
									(randy.nextInt(2) == 0 ? " East " : " West ") + 
									randy.nextInt(10000) +
									(randy.nextInt(2) == 0 ? " South" : " North")); 
			}else{
				patients[i][10]=patients[i][11]=patients[i][12]=patients[i][13]="";
			}
		}

		sb.append("INSERT INTO Patients (FirstName, LastName, MiddleInitial, Race, Gender, Insurer,\n" + 
				"DateOfBirth, BloodType, Religion, Country, StateProvince, City, PostalCode, Address)\n" +
				"VALUES ('" + patients[0][0] + 
				"', '" + patients[0][1] + 
				"', '" + patients[0][2] + 
				"', '" + patients[0][3] + 
				"', '" + patients[0][4] + 
				"', '" + patients[0][5] + 
				"', '" + patients[0][6] + 
				"', '" + patients[0][7] + 
				"', '" + patients[0][8] + 
				"', '" + patients[0][9] + 
				"', '" + patients[0][10] + 
				"', '" + patients[0][11] + 
				"', '" + patients[0][12] + 
				"', '" + patients[0][13] + "')");
		
		for(int i=1; i < numPatients; i++){
			sb.append(", \n('" + patients[i][0] + 
					"', '" + patients[i][1] + 
					"', '" + patients[i][2] + 
					"', '" + patients[i][3] + 
					"', '" + patients[i][4] + 
					"', '" + patients[i][5] + 
					"', '" + patients[i][6] + 
					"', '" + patients[i][7] + 
					"', '" + patients[i][8] + 
					"', '" + patients[i][9] + 
					"', '" + patients[i][10] + 
					"', '" + patients[i][11] + 
					"', '" + patients[i][12] + 
					"', '" + patients[i][13] + "')");
		}

		sb.append(";\n\n");
		
		byte[] sBytes = sb.toString().getBytes();
		try{
			os.write(sBytes);
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	//print the SQL for the patient allergies table to the output stream
	public static void ScriptPatientAllergies(OutputStream os){
		
		StringBuffer sb = new StringBuffer();

		//write stuff here
		sb.append("GO 1\n\n");
		
		sb.append("INSERT INTO PatientAllergies (PatientID, AllergyID)\n" +
				"VALUES(" +
				//get the patient
				"(SELECT TOP 1 p.PatientID FROM Patients p WHERE " +
				"p.FirstName = '" + patients[0][0] + "' AND " +
				"p.LastName = '" + patients[0][1] + "' AND " +
				"p.MiddleInitial = '" + patients[0][2] + "' AND " +
				"p.Country = '" + patients[0][9] + "' AND " +
				"p.PostalCode = '" + patients[0][12] + "'), " +
				
				//get the allergy
				"(SELECT TOP 1 a.AllergyID FROM Allergies a WHERE " +
				"a.AllergyName = '" + allergies[randy.nextInt(allergies.length)] +
				"'))");

		for(int i=0; i < numPatients; i++){
			int numAllergies = randy.nextInt(3);
			if(numAllergies > 0){
				
				//select random allergies
				int[] allers = {randy.nextInt(allergies.length), randy.nextInt(allergies.length)};

				//make sure our random allergies are different
				while(allers[0] == allers[1]){
					allers[1] = randy.nextInt(allergies.length);
				}
				
				for(int j=0; j<numAllergies; j++){
					sb.append(", \n(" +
							//get the patient
							"(SELECT TOP 1 p.PatientID FROM Patients p WHERE " +
							"p.FirstName = '" + patients[i][0] + "' AND " +
							"p.LastName = '" + patients[i][1] + "' AND " +
							"p.MiddleInitial = '" + patients[i][2] + "' AND " +
							"p.Country = '" + patients[i][9] + "' AND " +
							"p.PostalCode = '" + patients[i][12] + "'), " +
							
							//get the allergy
							"(SELECT TOP 1 a.AllergyID FROM Allergies a WHERE " +
							"a.AllergyName = '" + allergies[allers[j]] +
							"'))");
				}
			}
		}
		
		sb.append(";\n\n");

		byte[] sBytes = sb.toString().getBytes();
		try{
			os.write(sBytes);
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	//print the SQL for the patient visits table to the output stream
	public static void ScriptVisits(OutputStream os){
		
		StringBuffer sb = new StringBuffer();

		//write stuff here
		int doc = randy.nextInt(doctors.length);
		sb.append("INSERT INTO Visits (PatientID, DoctorID, DateOfVisit, ReasonForVisit, LocationID)\n" +
				"VALUES(" +
				//going through each patient...
				"(SELECT TOP 1 p.PatientID FROM Patients p WHERE " +
				"p.FirstName = '" + patients[0][0] + "' AND " +
				"p.LastName = '" + patients[0][1] + "' AND " +
				"p.MiddleInitial = '" + patients[0][2] + "' AND " +
				"p.Country = '" + patients[0][9] + "' AND " +
				"p.PostalCode = '" + patients[0][12] + "'), " +
				
				//select the Dr's ID from the table
				"(SELECT TOP 1 d.DoctorID FROM Doctors d WHERE " +
				"d.FirstName = '" + doctors[doc][0] + "' AND " +
				"d.LastName = '" + doctors[doc][1] + "' AND " +
				"d.MiddleInitial = '" + doctors[doc][2] + "' AND " +
				"d.Specialization = '" + doctors[doc][3] + "'), " +
				
				//make up a date of visit (in the last 10 years)
				"'" + (randy.nextInt(12)+1) + "/" + (randy.nextInt(28)+1) + "/" + (randy.nextInt(10)+2002) + "', " +
				
				//pick a reason for the visit
				"'" + visitReasons[randy.nextInt(visitReasons.length)] + "', " +
				
				//select the Location ID from the table
				"(SELECT TOP 1 l.LocationID FROM Locations l WHERE " +
				"l.Name = '" + locations[randy.nextInt(locations.length)] + 

				"'))");
		for(int i=0; i < numPatients; i++){
			int numVisits = randy.nextInt(4)+1;

			for(int j=0; j<numVisits; j++){
				doc = randy.nextInt(doctors.length);
				sb.append(", \n(" +
						//going through each patient...
						"(SELECT TOP 1 p.PatientID FROM Patients p WHERE " +
						"p.FirstName = '" + patients[i][0] + "' AND " +
						"p.LastName = '" + patients[i][1] + "' AND " +
						"p.MiddleInitial = '" + patients[i][2] + "' AND " +
						"p.Country = '" + patients[i][9] + "' AND " +
						"p.PostalCode = '" + patients[i][12] + "'), " +
						
						//select the Dr's ID from the table
						"(SELECT TOP 1 d.DoctorID FROM Doctors d WHERE " +
						"d.FirstName = '" + doctors[doc][0] + "' AND " +
						"d.LastName = '" + doctors[doc][1] + "' AND " +
						"d.MiddleInitial = '" + doctors[doc][2] + "' AND " +
						"d.Specialization = '" + doctors[doc][3] + "'), " +
						
						//make up a date of visit (in the last 10 years)
						"'" + (randy.nextInt(12)+1) + "/" + (randy.nextInt(28)+1) + "/" + (randy.nextInt(10)+2002) + "', " +
						
						//pick a reason for the visit
						"'" + visitReasons[randy.nextInt(visitReasons.length)] + "', " +
						
						//select the Location ID from the table
						"(SELECT TOP 1 l.LocationID FROM Locations l WHERE " +
						"l.Name = '" + locations[randy.nextInt(locations.length)] + 

						"'))");
			}
		}
		
		sb.append(";\n\n");

		byte[] sBytes = sb.toString().getBytes();
		try{
			os.write(sBytes);
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	//print the SQL for the patient medications table to the output stream
	public static void ScriptPatientMeds(OutputStream os){
		
		StringBuffer sb = new StringBuffer();

		//write stuff here
		int doc = randy.nextInt(doctors.length);
		sb.append("INSERT INTO PatientMeds (PatientID, MedicationID, Dosage, DoctorID)\n" +
				"VALUES(" +
				//going through each patient...
				"(SELECT TOP 1 p.PatientID FROM Patients p WHERE " +
				"p.FirstName = '" + patients[0][0] + "' AND " +
				"p.LastName = '" + patients[0][1] + "' AND " +
				"p.MiddleInitial = '" + patients[0][2] + "' AND " +
				"p.Country = '" + patients[0][9] + "' AND " +
				"p.PostalCode = '" + patients[0][12] + "'), " +
				
				//select the medication from the table
				"(SELECT TOP 1 m.MedicationID FROM Medications m WHERE " +
				"m.Name = '" + medicationNames[randy.nextInt(medicationNames.length)] + "'), " +
				
				//create a dosage
				"'" + ((randy.nextInt(10)+1)*100) + "mg', " +
				
				//select the Dr's ID from the table
				"(SELECT TOP 1 d.DoctorID FROM Doctors d WHERE " +
				"d.FirstName = '" + doctors[doc][0] + "' AND " +
				"d.LastName = '" + doctors[doc][1] + "' AND " +
				"d.MiddleInitial = '" + doctors[doc][2] + "' AND " +
				"d.Specialization = '" + doctors[doc][3] + 
				"'))");
		for(int i=1; i < numPatients; i++){
			int numMeds = randy.nextInt(4);
			if(numMeds > 0){
				
				//select random Medications
				int[] meds = {randy.nextInt(medicationNames.length), randy.nextInt(medicationNames.length),randy.nextInt(medicationNames.length)};

				//make sure our random meds are different
				while(meds[0] == meds[1]){
					meds[1] = randy.nextInt(medicationNames.length);
				}
				while(meds[0] == meds[2] || meds[1] == meds[2]){
					meds[2] = randy.nextInt(medicationNames.length);
				}
				
				for(int j=0; j<numMeds; j++){
					doc = randy.nextInt(doctors.length);
					sb.append(", \n(" +
							//going through each patient...
							"(SELECT TOP 1 p.PatientID FROM Patients p WHERE " +
							"p.FirstName = '" + patients[i][0] + "' AND " +
							"p.LastName = '" + patients[i][1] + "' AND " +
							"p.MiddleInitial = '" + patients[i][2] + "' AND " +
							"p.Country = '" + patients[i][9] + "' AND " +
							"p.PostalCode = '" + patients[i][12] + "'), " +
							
							//select the medication from the table
							"(SELECT TOP 1 m.MedicationID FROM Medications m WHERE " +
							"m.Name = '" + medicationNames[meds[j]] + "'), " +
							
							//create a dosage
							"'" + ((randy.nextInt(10)+1)*100) + "mg', " +
							
							//select the Dr's ID from the table
							"(SELECT TOP 1 d.DoctorID FROM Doctors d WHERE " +
							"d.FirstName = '" + doctors[doc][0] + "' AND " +
							"d.LastName = '" + doctors[doc][1] + "' AND " +
							"d.MiddleInitial = '" + doctors[doc][2] + "' AND " +
							"d.Specialization = '" + doctors[doc][3] + 
							"'))");
				}
			}
		}

		sb.append(";\n\n");

		byte[] sBytes = sb.toString().getBytes();
		try{
			os.write(sBytes);
		}catch(IOException e){
			e.printStackTrace();
		}
	}
	
	/**
	 * @param args command line arguments (unused)
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		File f = null;
		FileOutputStream out = null;
		
		try {
			 
			f = new File("./dbscript.sql");
			out = new FileOutputStream(f);
 
			// if file doesn't exist, then create it
			if (!f.exists()) {
				f.createNewFile();
			}
 
			//write the script
			ScriptDeletes(out);
			ScriptAllergies(out);
			ScriptLocations(out);
			ScriptDoctors(out);
			ScriptMedications(out);
			ScriptPatients(out);
			ScriptPatientAllergies(out);
			ScriptVisits(out);
			ScriptPatientMeds(out);
			
			//output the file
			out.flush();
			//save and close the file
			out.close();
 
			//print that we're done
			System.out.println("Done");
 
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (out != null) {
					out.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

	}

}
